import { paginatedResponse, errorResponse } from 'server/utils/response'
import { pool } from 'server/database/config'

export default defineEventHandler(async (event) => {
  try {
    const { slug } = getRouterParams(event)
    const query = getQuery(event)
    const page = parseInt(query.page as string) || 1
    const pageSize = Math.min(parseInt(query.pageSize as string) || 10, 20)

    if (!slug) {
      return errorResponse('标签标识不能为空')
    }

    // 获取标签信息
    const [tagRows]: any = await pool.execute(
      'SELECT id, name FROM tags WHERE slug = ?',
      [slug]
    )

    if (tagRows.length === 0) {
      return errorResponse('标签不存在')
    }

    const tagId = tagRows[0].id
    const tagName = tagRows[0].name

    // 计算偏移量
    const offset = (page - 1) * pageSize

    // 查询包含该标签的项目总数
    const [countRows]: any = await pool.execute(
      `SELECT COUNT(DISTINCT p.id) as total
       FROM projects p
       JOIN project_tags pt ON p.id = pt.project_id
       WHERE pt.tag_id = ? AND p.status = 'published'`,
      [tagId]
    )

    const total = countRows[0].total
    const totalPages = Math.ceil(total / pageSize)

    // 查询包含该标签的项目
    const [projectRows]: any = await pool.execute(
      `SELECT p.*
       FROM projects p
       JOIN project_tags pt ON p.id = pt.project_id
       WHERE pt.tag_id = ? AND p.status = 'published'
       ORDER BY p.created_at DESC
       LIMIT ? OFFSET ?`,
      [tagId, pageSize, offset]
    )

    // 处理项目数据，添加封面图片URL
    const projects = await Promise.all(projectRows.map(async (project: any) => {
      // 如果有封面图片，获取图片URL
      let cover_image_url = ''
      if (project.cover_image_id) {
        try {
          const [fileRows]: any = await pool.execute(
            'SELECT file_path FROM files WHERE id = ? AND status = "active"',
            [project.cover_image_id]
          )
          if (fileRows.length > 0) {
            cover_image_url = `/uploads/${fileRows[0].file_path}`
          }
        } catch (error) {
          console.error('获取封面图片失败:', error)
        }
      }

      return {
        ...project,
        technologies: project.technologies ? JSON.parse(project.technologies) : [],
        cover_image_url
      }
    }))

    return paginatedResponse(
      projects,
      {
        page,
        pageSize,
        total,
        totalPages
      },
      `获取标签"${tagName}"相关项目成功`
    )
  } catch (error) {
    console.error('获取标签相关文章失败:', error)
    return errorResponse('获取标签相关文章失败', error instanceof Error ? error.message : '未知错误')
  }
})